package com.sailfish.springbootdemo.utils;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.IOException;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {
    public static List<LinkedHashMap<String, String>> parseExcelSheet0(MultipartFile file) {
        List<LinkedHashMap<String, String>> rows = new ArrayList<>();
        if (file == null || file.isEmpty()) {
            return rows; // 文件为空直接返回空列表
        }

        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(0);
            if (sheet == null || sheet.getLastRowNum() < 0) {
                return rows; // Sheet 为空或无数据
            }

            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                return rows; // 表头为空
            }

            // 提取列名
            List<String> columns = new ArrayList<>();
            for (Cell cell : headerRow) {
                columns.add(getCellValue(cell).trim()); // 使用 getCellValue 处理空值
            }
            if (columns.isEmpty()) {
                return rows; // 表头无有效列
            }

            // 提取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null) {
                    continue; // 跳过空行
                }

                LinkedHashMap<String, String> rowData = new LinkedHashMap<>();
                for (int j = 0; j < columns.size(); j++) {
                    Cell cell = row.getCell(j);
                    if(cell == null)
                    {
                        rowData.put(columns.get(j), ""); // 即使为空也添加键值对
                    }else
                    {
                        rowData.put(columns.get(j), getCellValue(cell));
                    }// 获取单元格值，空值返回空字符串
                }
                rows.add(rowData);
            }
        } catch (IOException e) {
            throw new RuntimeException("Failed to parse Excel file: " + e.getMessage(), e); // 抛出异常给调用者
        }
        return rows;
    }

    public static String getCellValue(Cell cell) {
        String cellValue = "";
        // 以下是判断数据的类型
        switch (cell.getCellTypeEnum()) {
            case NUMERIC: // 数字
                if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                    cellValue = sdf.format(org.apache.poi.ss.usermodel.DateUtil.getJavaDate(cell.getNumericCellValue())).toString();
                } else {
                    DataFormatter dataFormatter = new DataFormatter();
                    cellValue = dataFormatter.formatCellValue(cell);
                }
                break;
            case STRING: // 字符串
                cellValue = cell.getStringCellValue();
                break;
            case BOOLEAN: // Boolean
                cellValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA: // 公式
                cellValue = cell.getCellFormula() + "";
                break;
            case BLANK: // 空值
                cellValue = "";
                break;
            case ERROR: // 故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

    public static List<Map<String, String>> parseExcel2(MultipartFile file,Integer sheetIndex,Integer headerIndex) {
        List<Map<String, String>> rows = new ArrayList<>();
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row headerRow = sheet.getRow(headerIndex);

            // 提取列名
            List<String> columns = new ArrayList<>();
            for (Cell cell : headerRow) {
                columns.add(cell.getStringCellValue().trim());
            }

            // 提取数据行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, String> rowData = new HashMap<>();
                    boolean allCellsEmpty = true;  // 新增整行空值判断标志

                    for (int j = 0; j < columns.size(); j++) {
                        // 使用CREATE_NULL_AS_BLANK策略避免空指针[5](@ref)
                        Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

                        String cellValue = getCellValue(cell);
                        if(cellValue==null||cellValue.isEmpty())
                        {
                            rowData.put(columns.get(j), "N/A");
                        }
                        else
                        {
                            rowData.put(columns.get(j), cellValue);
                        }

                        // 判断单元格是否实质性空值（包含空字符串等）
                        if (!isCellValueEmpty(cellValue)) {
                            allCellsEmpty = false;
                        }
                    }

                    // 仅当存在有效值时添加行[2,6](@ref)
                    if (!allCellsEmpty) {
                        rows.add(rowData);
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static List<Map<String, String>> parseExcel(MultipartFile file, Integer sheetIndex, Integer headerIndex,Integer cellIndex) {
        List<Map<String, String>> rows = new ArrayList<>();
        try (Workbook workbook = WorkbookFactory.create(file.getInputStream())) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row headerRow = sheet.getRow(headerIndex);
            int lastColumn = headerRow.getLastCellNum(); // 获取表头最大列数

            // 提取列名
            List<String> columns = new ArrayList<>();
            for (int j = cellIndex; j < lastColumn; j++) { // 关键修改：从cellIndex遍历列
                Cell cell = headerRow.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                columns.add(getCellValue2(cell)); // 使用通用单元格值获取方法
            }

            // 提取数据行
            for (int i = headerIndex+1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, String> rowData = new HashMap<>();
                    boolean allCellsEmpty = true;  // 新增整行空值判断标志

                    for (int j = 0; j < columns.size(); j++) {
                        // 计算实际列索引 = 起始列 + 当前偏移量
                        int actualColIndex = cellIndex + j;
                        Cell cell = row.getCell(actualColIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        rowData.put(columns.get(j), getCellValue(cell));

                        // 判断单元格是否实质性空值（包含空字符串等）
                        if (!isCellValueEmpty(getCellValue(cell))) {
                            allCellsEmpty = false;
                        }
                    }
                    if (!allCellsEmpty) {
                        rows.add(rowData);
                    }
                    //rows.add(rowData);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static List<Map<String, String>> parseExcel3(File file, Integer sheetIndex, Integer headerIndex, Integer cellIndex) {
        List<Map<String, String>> rows = new ArrayList<>();
        try (Workbook workbook = WorkbookFactory.create(file)) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            Row headerRow = sheet.getRow(headerIndex);
            int lastColumn = headerRow.getLastCellNum(); // 获取表头最大列数

            // 提取列名
            List<String> columns = new ArrayList<>();
            for (int j = cellIndex; j < lastColumn; j++) { // 关键修改：从cellIndex遍历列
                Cell cell = headerRow.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                columns.add(getCellValue2(cell)); // 使用通用单元格值获取方法
            }

            // 提取数据行
            for (int i = headerIndex+1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    Map<String, String> rowData = new HashMap<>();
                    boolean allCellsEmpty = true;  // 新增整行空值判断标志

                    for (int j = 0; j < columns.size(); j++) {
                        // 计算实际列索引 = 起始列 + 当前偏移量
                        int actualColIndex = cellIndex + j;
                        Cell cell = row.getCell(actualColIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        rowData.put(columns.get(j), getCellValue(cell));

                        // 判断单元格是否实质性空值（包含空字符串等）
                        if (!isCellValueEmpty(getCellValue(cell))) {
                            allCellsEmpty = false;
                        }
                    }
                    if (!allCellsEmpty) {
                        rows.add(rowData);
                    }
                    //rows.add(rowData);
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return rows;
    }

    public static String getCellValue2(Cell cell) {
        if (cell == null) return "";
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue().trim();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) { // 日期类型
                    return new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
                } else { // 数值类型
                    // 避免科学计数法（如长数字转字符串）
                    return NumberToTextConverter.toText(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA: // 公式类型
                try {
                    return cell.getStringCellValue(); // 尝试按字符串解析公式结果
                } catch (IllegalStateException e) {
                    return String.valueOf(cell.getNumericCellValue()); // 按数值解析公式结果
                }
            case BLANK:
                return "";
            default:
                return "";
        }
    }

    private static boolean isCellValueEmpty(String value) {
        return value == null || value.trim().isEmpty();
    }

    public static XSSFCellStyle getDateStyle(Workbook workbook) {
        XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
        setBorder.setBorderBottom(BorderStyle.THIN);
        setBorder.setBorderLeft(BorderStyle.THIN);
        setBorder.setBorderRight(BorderStyle.THIN);
        setBorder.setBorderTop(BorderStyle.THIN);
        setBorder.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder.setAlignment(HorizontalAlignment.CENTER);
        setBorder.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        setBorder.setWrapText(true);
        return setBorder;
    }

    public static XSSFCellStyle getNormalStyle(Workbook workbook) {
        XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
        setBorder.setBorderBottom(BorderStyle.THIN);
        setBorder.setBorderLeft(BorderStyle.THIN);
        setBorder.setBorderRight(BorderStyle.THIN);
        setBorder.setBorderTop(BorderStyle.THIN);
        setBorder.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder.setAlignment(HorizontalAlignment.CENTER);
        setBorder.setWrapText(true);
        return setBorder;
    }

    public static XSSFCellStyle getBoldStyle(Workbook workbook) {
        XSSFCellStyle setBorder = (XSSFCellStyle) workbook.createCellStyle();
        setBorder.setBorderBottom(BorderStyle.THIN);
        setBorder.setBorderLeft(BorderStyle.THIN);
        setBorder.setBorderRight(BorderStyle.THIN);
        setBorder.setBorderTop(BorderStyle.THIN);
        setBorder.setVerticalAlignment(VerticalAlignment.CENTER);
        setBorder.setAlignment(HorizontalAlignment.CENTER);
        //创建字体 加粗
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setBold(true);
        font.setFontHeight(12);
        setBorder.setFont(font);
        setBorder.setWrapText(true);
        return setBorder;
    }
}
